﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommonsLib.Utility;
using System.Data.SqlClient;
using MaxLeaf.Platform.Functions;
using System.Data.Common;
using MaxLeaf.Platform.Commons;
using MaxLeaf.Platform.DataContract;
using CommonsLib.DB;

namespace MaxLeaf.Platform.DALImplementation
{
    public class imStoresReqOverviewQryByItemDAL:BaseDAL
    {
 

       

        //private static object GetCanApproBill(object model)
        //{

        //    return string.Format("dbo.CanApproBill(h.finterid, {0}, {1}) = 1",   "h.CompanyID" , DataFunc.GetPropValue(model, "UI_UserID"));
        //}

         

        internal static string ItemTableName(object model)
        {
            string strSql = string.Format(@"
                (
                    select 0 as KeyID,H.CompanyID
                    ,D.ItemID
                    ,isnull(g_k.usegoods_name,'') as Keywords
                    ,g.goods_name as GoodsName
                    ,g.spec as spec
                     ,isnull(g_b.brand_name,'') as BrandName
                    ,g.sku_num as SKU
                    ,isnull({1}F_GetItemAttrValue(D.ItemID),'') as GoodsAttr
                    ,isnull(g_u.measure_name,'') as UnitName
                    ,D.WebPrice
                    ,D.Price
                    ,D.UnitID
                    ,isnull(loc.onhand,0) as onhand
                    ,isnull(loc.Min,0) as Min
                    ,isnull(loc.Max,0) as Max

                    ,sum(d.ReqQty) as ReqQty
                    ,sum(d.Subtotal) as Subtotal
                    ,Count(distinct(d.ItemID)) as GoodsTypeCount

                    from {3}
                    inner join  {0}imStoresReqDtl D  on h.finterid=d.hdrid
                    inner join {1}ImInvMaster i on d.itemid=i.FinterID
                    inner join {1}goods_main g on g.goods_id=i.GoodsID
                    left join {2}ImInvloc loc on d.itemid=loc.itemid  and  loc.companyid=H.CompanyID
                    
                    left join {1}goods_UserKeywords g_k on g_k.company_id=H.CompanyID and i.FinterID=g_k.ItemID
                    left join {1}goods_brand g_b on g_b.brand_id=g.brand_id
                    left join {1}goods_attribute g_a on g_a.goods_attr_id=i.GoodsAttrID
                    left join {1}goods_measure_unit g_u on g_u.measure_unit_id=D.UnitID
                    where  1=1
                    {4}
                    group by 
                    H.CompanyID
                    ,D.ItemID
                    ,isnull(g_k.usegoods_name,'') 
                    ,g.goods_name  
                    ,g.spec  
                     ,isnull(g_b.brand_name,'')  
                    ,g.sku_num  
                    ,isnull({1}F_GetItemAttrValue(D.ItemID),'')
                    ,isnull(g_u.measure_name,'')  
                    ,D.WebPrice
                    ,D.Price
                    ,D.UnitID
                    ,isnull(loc.onhand,0)
                    ,isnull(loc.Min,0)
                    ,isnull(loc.Max,0)
                ) a"
                    , GetOrdDatabase()
                    , GetBaseDatabase()
                    ,GetInvDatabase()
                   , imStoresReqHdrDAL.OverviewTableName(model)
                   , GetCond(DataFunc.GetPropValue(model, "UI_Conds"))
                    );

            return strSql;      
        }

        internal static string DepTableName(object model)
        {
            string strSql = string.Format(@"
                (
                    select 0 as KeyID,H.CompanyID
                    ,D.ItemID
                    ,isnull(g_k.usegoods_name,'') as Keywords
                    ,g.goods_name as GoodsName
                    ,g.spec as spec
                     ,isnull(g_b.brand_name,'') as BrandName
                    ,g.sku_num as SKU
                    ,isnull({1}F_GetItemAttrValue(D.ItemID),'') as GoodsAttr
                    ,isnull(g_u.measure_name,'') as UnitName
                    ,D.WebPrice
                    ,D.Price
                    ,D.UnitID
                    ,dep.DepID       
                    ,dep.DepName
                    ,isnull(loc.onhand,0) as onhand
                    ,isnull(loc.Min,0) as Min
                    ,isnull(loc.Max,0) as Max
                    ,sum(d.ReqQty) as ReqQty
                    ,sum(d.Subtotal) as Subtotal
                    ,Count(distinct(d.ItemID)) as GoodsTypeCount

                    from {3}
                    inner join  {0}imStoresReqDtl D  on h.finterid=d.hdrid
                    inner join {1}ImInvMaster i on d.itemid=i.FinterID
                    left join {2}ImInvloc loc on d.itemid=loc.itemid  and  loc.companyid=H.CompanyID
                    inner join {1}goods_main g on g.goods_id=i.GoodsID
                    inner join {1}mDepartment dep on dep.depid=H.DepartID

                    left join {1}goods_UserKeywords g_k on g_k.company_id=H.CompanyID and i.FinterID=g_k.ItemID
                    left join {1}goods_brand g_b on g_b.brand_id=g.brand_id
                    left join {1}goods_attribute g_a on g_a.goods_attr_id=i.GoodsAttrID
                    left join {1}goods_measure_unit g_u on g_u.measure_unit_id=D.UnitID
                    where 1=1
                     {4}
                    group by 
                    H.CompanyID
                    ,D.ItemID
                    ,isnull(g_k.usegoods_name,'') 
                    ,g.goods_name  
                    ,g.spec  
                     ,isnull(g_b.brand_name,'')  
                    ,g.sku_num  
                    ,isnull({1}F_GetItemAttrValue(D.ItemID),'')
                    ,isnull(g_u.measure_name,'')  
                    ,D.WebPrice
                    ,D.Price
                    ,D.UnitID
                    ,dep.DepID       
                    ,dep.DepName 
                    ,isnull(loc.onhand,0)
                    ,isnull(loc.Min,0)
                    ,isnull(loc.Max,0)
                ) a"
                    , GetOrdDatabase()
                    , GetBaseDatabase()
                    ,GetInvDatabase()
                   , imStoresReqHdrDAL.OverviewTableName(model)
                   , GetCond(DataFunc.GetPropValue(model, "UI_Conds"))
                    );

            return strSql;
        }

        internal static string OrdTableName(object model)
        {
            string strSql = string.Format(@"
                (
                    select 0 as KeyID,H.CompanyID
                    ,D.ItemID
                    ,isnull(g_k.usegoods_name,'') as Keywords
                    ,g.goods_name as GoodsName
                    ,g.spec as spec
                     ,isnull(g_b.brand_name,'') as BrandName
                    ,g.sku_num as SKU
                    ,isnull({1}F_GetItemAttrValue(D.ItemID),'') as GoodsAttr
                    ,isnull(g_u.measure_name,'') as UnitName
                    ,D.WebPrice
                    ,D.Price
                    ,D.UnitID
                    ,dep.DepID       
                    ,dep.DepName
                    ,H.OrdNo
                    ,H.DocDate
                    ,H.DocType as SCPCategoryID
                    ,isnull(scp.SCPCategoryName ,'') as SCPCategoryName
                    ,H.ReqUserID
                    ,u.UserName as ReqUserName
                    ,isnull(loc.onhand,0) as onhand
                    ,isnull(loc.Min,0) as Min
                    ,isnull(loc.Max,0) as Max

                    ,sum(d.ReqQty) as ReqQty
                    ,sum(d.Subtotal) as Subtotal
                    ,d_sum.TotAmt as TotAmt
                    ,Count(distinct(d.ItemID)) as GoodsTypeCount

                    from {3}
                    inner join  {0}imStoresReqDtl D  on h.finterid=d.hdrid
                    inner join (select hdrid,sum(Subtotal) as TotAmt from {0}imStoresReqDtl where  posted=0 and  is_delete=0  group by hdrid) D_sum  on D_sum.hdrid=d.hdrid
                    inner join {1}ImInvMaster i on d.itemid=i.FinterID
                    left join {2}ImInvloc loc on d.itemid=loc.itemid  and  loc.companyid=H.CompanyID
                    inner join {1}goods_main g on g.goods_id=i.GoodsID
                    inner join {1}mDepartment dep on dep.depid=H.DepartID
                    left join {1}SCPCategory scp on scp.SCPCategorySeqNo=H.DocType and  scp.companyid=H.CompanyID
                    inner join {1}muser_main u on u.userid=H.ReqUserID

                    left join {1}goods_UserKeywords g_k on g_k.company_id=H.CompanyID and i.FinterID=g_k.ItemID
                    left join {1}goods_brand g_b on g_b.brand_id=g.brand_id
                    left join {1}goods_attribute g_a on g_a.goods_attr_id=i.GoodsAttrID
                    left join {1}goods_measure_unit g_u on g_u.measure_unit_id=D.UnitID
                    where 1=1
                    {4}
                    group by 
                    H.CompanyID
                    ,D.ItemID
                    ,isnull(g_k.usegoods_name,'') 
                    ,g.goods_name  
                    ,g.spec  
                     ,isnull(g_b.brand_name,'')  
                    ,g.sku_num  
                    ,isnull({1}F_GetItemAttrValue(D.ItemID),'')
                    ,isnull(g_u.measure_name,'')  
                    ,D.WebPrice
                    ,D.Price
                    ,D.UnitID
                    ,d_sum.TotAmt
                    ,dep.DepID       
                    ,dep.DepName 
                    ,H.OrdNo
                    ,H.DocDate
                    ,H.DocType 
                    ,isnull(scp.SCPCategoryName ,'')
                    ,H.ReqUserID
                    ,u.UserName 
,isnull(loc.onhand,0)
                    ,isnull(loc.Min,0)
                    ,isnull(loc.Max,0)
                ) a"
                    , GetOrdDatabase()
                    , GetBaseDatabase()
                    , GetInvDatabase()
                    , imStoresReqHdrDAL.OverviewTableName(model)
                    , GetCond(DataFunc.GetPropValue(model, "UI_Conds"))
                    );

            return strSql;
        }
        private static string GetCond(object objConds)
        {
            List<Cond> lstCond = objConds as List<Cond>;
            if (lstCond == null || lstCond.Count == 0)
            {
                return string.Empty;
            }

            string strCond = string.Empty;
            Dictionary<string, object> dictCond = new Dictionary<string, object>();
            foreach (Cond cond in lstCond)
            {
                if (cond.PropName.StartsWith("Func_")) continue;
                if (cond.PropName.StartsWith("UI_")) continue;

                string strFieldName = cond.PropName;
                if (string.IsNullOrEmpty(strFieldName))
                {
                    throw new MaxLeaf.Platform.Commons.MaxLeafException(string.Format("不存在 {0}", cond.PropName));
                }
                switch (strFieldName)
                {
                    default:
                        strFieldName = string.Format("h.{0}", strFieldName);
                        break;
                }

                strCond += string.Format(" and {0} {1} '{2}'  "
                    , strFieldName
                    , BaseDAL.GetCompare(cond.Compare)
                    , cond.Value
                    );
                if (cond.Value == null)
                {
                    throw new MaxLeaf.Platform.Commons.MaxLeafException(string.Format("{0} 值为 null", cond.PropName));
                }

            }
            return strCond;
        }
        
    }
}
